{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "列 a1 的加权平均距离的平均值是: 2.69\n",
      "列 a2 的加权平均距离的平均值是: 1.92\n",
      "列 a3 的加权平均距离的平均值是: 1.95\n",
      "列 a4 的加权平均距离的平均值是: 1.84\n",
      "列 a5 的加权平均距离的平均值是: 2.59\n",
      "  Column  Average Weighted Distance\n",
      "0     a1                   2.689796\n",
      "1     a2                   1.922449\n",
      "2     a3                   1.951020\n",
      "3     a4                   1.836735\n",
      "4     a5                   2.591837\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# 加载CSV文件\n",
    "csv_file_path = \"updata/test.csv\"\n",
    "df = pd.read_csv(csv_file_path)\n",
    "\n",
    "# 动态检测列名，从第二列开始，直到第一个空列名\n",
    "def get_non_empty_column_names(df):\n",
    "    columns = df.columns\n",
    "    non_empty_columns = []\n",
    "\n",
    "    for col in columns[1:]:  # 从第二列开始\n",
    "        if col.strip() == '':  # 如果遇到空列名，停止检测\n",
    "            break\n",
    "        non_empty_columns.append(col)\n",
    "    \n",
    "    return non_empty_columns\n",
    "# 获取1到x列，x是第一个空列名之前的列\n",
    "non_empty_columns = get_non_empty_column_names(df)\n",
    "\n",
    "# 确保base列存在\n",
    "if 'base' not in non_empty_columns:\n",
    "    raise ValueError(\"CSV文件中没有找到 'base' 列\")\n",
    "\n",
    "def calculate_weighted_average_distance_top3_gradual(list1, list2):\n",
    "    if len(list1) != len(list2):\n",
    "        raise ValueError(\"两个输入列表必须长度相同。\")\n",
    "\n",
    "    n = len(list1)\n",
    "    # 设置前三个位置的权重大，其他位置的权重按递减的方式分配\n",
    "    initial_weight = 10\n",
    "    decay_rate = 0.8\n",
    "    weights = np.array([initial_weight if i < 3 else initial_weight * decay_rate**(i-3) for i in range(n)])\n",
    "\n",
    "    position_dict = {item: index for index, item in enumerate(list1)}\n",
    "\n",
    "    distances = []\n",
    "    for index, item in enumerate(list2):\n",
    "        if item in position_dict:\n",
    "            distance = abs(index - position_dict[item])\n",
    "            distances.append(distance)\n",
    "        else:\n",
    "            raise ValueError(f\"项目 {item} 在 list1 中未找到。\")\n",
    "\n",
    "    weighted_distances = [d * w for d, w in zip(distances, weights)]\n",
    "    weighted_average_distance = sum(weighted_distances) / sum(weights)\n",
    "\n",
    "    return weighted_average_distance\n",
    "\n",
    "\n",
    "\n",
    "# 存储每列的加权平均距离\n",
    "results = {col: [] for col in non_empty_columns if col != 'base'}\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    try:\n",
    "        base_list = row['base'][1:-1].split(', ')  # 去掉中括号并分割字符串\n",
    "        base_list = list(map(int, base_list))  # 转换为整数列表\n",
    "    except:\n",
    "        continue  # 如果 'base' 列格式不对，跳过该行\n",
    "    \n",
    "    for col in non_empty_columns:\n",
    "        if col != 'base':\n",
    "            try:\n",
    "                compare_list = row[col][1:-1].split(', ')  # 去掉中括号并分割字符串\n",
    "                compare_list = list(map(int, compare_list))  # 转换为整数列表\n",
    "                \n",
    "                # 确保两个列表的长度相同\n",
    "                min_length = min(len(base_list), len(compare_list))\n",
    "                base_list_trimmed = base_list[:min_length]\n",
    "                compare_list_trimmed = compare_list[:min_length]\n",
    "                \n",
    "                distance = calculate_weighted_average_distance(base_list_trimmed, compare_list_trimmed)\n",
    "                results[col].append(distance)\n",
    "            except:\n",
    "                # 处理可能的数据格式错误\n",
    "                continue\n",
    "\n",
    "# 计算每列的加权平均距离的平均值\n",
    "average_distances = {col: np.mean(distances) for col, distances in results.items()}\n",
    "\n",
    "# 打印结果\n",
    "for col, avg_distance in average_distances.items():\n",
    "    print(f\"列 {col} 的加权平均距离的平均值是: {avg_distance:.2f}\")\n",
    "\n",
    "# 如果需要以 DataFrame 的形式显示结果\n",
    "average_distances_df = pd.DataFrame(list(average_distances.items()), columns=['Column', 'Average Weighted Distance'])\n",
    "print(average_distances_df)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pytorch",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.17"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
